using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BLGenerator.BusinessLogic;

namespace BLGenerator.DataLayer
{
    public class SQLDataLayer
    {
        public static Report RefreshEntityCollection(string connectionString, Project project)
        {
            EntityCollection newEntities = new EntityCollection();
            Report report = new Report();
            using (SqlConnection connection = GetConnection(connectionString))
            {
                using (SqlCommand command = new SqlCommand("SELECT name FROM sysobjects WHERE xtype='U' ORDER BY name", connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            string tableName = reader.GetString(0);
                            if (project.EntityCollection.HasTableName(tableName)) continue;
                            Entity entity = new Entity(tableName, tableName, false, project);
                            project.EntityCollection.Add(entity);
                            newEntities.Add(entity);
                            report.Add(string.Format("Entity [{0}] added", tableName));
                        }
                    }
                }

                foreach (Entity newEntity in newEntities)
                {
                    FillEntity(newEntity, connection);
                    if (newEntity.PrimaryFields.Count == 0)
                        report.Add(string.Format("Entity [{0}] doesn't have Primary Fields", newEntity.EntityName));
                }
            }
            return report;
        }

        protected static void FillEntity(Entity entity, SqlConnection sqlConnection)
        {
            using (SqlCommand command = new SqlCommand(string.Format("SELECT TOP 1 * FROM [{0}]", entity.TableName), sqlConnection))
            {
                using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo))
                {
                    DataTable table = reader.GetSchemaTable();
                    List<string> ss = new List<string>();
                    foreach (DataColumn column in table.Columns)
                    {
                        ss.Add(column.ColumnName);
                    }
                    foreach (DataRow row in table.Rows)
                    {
                        string name = (string)row["ColumnName"];
                        bool isNullable = (bool)row["AllowDBNull"];
                        Type type = (Type) row["DataType"];
                        bool isAutoGenerated = (bool)row["IsIdentity"];

                        Field field = new Field(name, type, isAutoGenerated, isNullable);
                        entity.FieldCollection.Add(field);
                        if ((bool)row["IsKey"]) entity.PrimaryFields.Add(field);
                    }
                }
            }
        }

        protected static SqlConnection GetConnection(string connectionString)
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }
    }
}